package de.wittekind.bundesjugendspiele.server.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import sun.reflect.generics.reflectiveObjects.NotImplementedException;
import de.wittekind.bundesjugendspiele.server.DatabaseConnectionManager;
import de.wittekind.bundesjugendspiele.server.calc.PointCalculator;
import de.wittekind.bundesjugendspiele.server.interfaces.Discipline;
import de.wittekind.bundesjugendspiele.server.interfaces.Schueler;
import de.wittekind.bundesjugendspiele.server.interfaces.Try;
import de.wittekind.bundesjugendspiele.server.interfaces.Unit;

/**
 * @author Claudius Ellsel
 * 
 * @version 07.05.2013
 */

public class SQLSchueler implements Schueler {

	private int id;

	public SQLSchueler(int id) throws IOException {
		this.setId(id);
	}

	@Override
	public int getID() throws IOException {
		// try {
		// Connection c = DatabaseConnectionManager.getConnection();
		// Statement s = c.createStatement();
		// String sql = "select ID from Schueler where id=" + id;
		// ResultSet rs = s.executeQuery(sql);
		// rs.next();
		// int amount = rs.getInt(1);
		// s.close();
		// return amount;
		// } catch (SQLException ex) {
		// throw new IOException(ex.getMessage());
		// }
		return id;
	}

	@Override
	public String getLastName() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select nachname from schueler where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			String amount = rs.getString(1);
			s.close();
			return amount;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public String getFirstName() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select vorname from schueler where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			String amount = rs.getString(1);
			s.close();
			return amount;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public GregorianCalendar getBirthday() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select tag,monat,jahr from schueler where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int tag = rs.getInt(1);
			int monat = rs.getInt(2) - 1;
			int jahr = rs.getInt(3);
			GregorianCalendar cal = new GregorianCalendar(jahr, monat, tag);
			s.close();
			return cal;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public int getPoints() throws IOException {
		// TODO ändern
		try {
			return PointCalculator.getPoints(getTries(), getGender());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	@Deprecated
	public int getTry() throws IOException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Try[] getTries() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select id, disziplin, wert, einheit from versuch where s_id="
					+ id;
			ResultSet rs = s.executeQuery(sql);
			List<Try> tries = new ArrayList<Try>();
			while (rs.next()) {
				int id = rs.getInt(1);
				Discipline discipline = Discipline.values()[rs.getInt(2)];
				double value = rs.getDouble(3);
				Unit unit = Unit.values()[rs.getInt(4)];
				tries.add(new SQLTry(id, value, unit, discipline));
			}
			s.close();
			return tries.toArray(new Try[] {});
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	@Deprecated
	public int getCurrentDiscipline() throws IOException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Gender getGender() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select geschlecht from schueler where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			Gender rf = Gender.values()[rs.getInt(1)];
			s.close();
			return rf;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public String getForm() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select klasse from schueler where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			String rf = rs.getString(1);
			s.close();
			return rf;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	@Deprecated
	public void setTry(int nTry) throws IOException {
		throw new NotImplementedException();
	}

	@Deprecated
	@Override
	public void setTries(Try[] tries) throws IOException {
		throw new NotImplementedException();
	}

	@Override
	public Try createNewTry() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();

			// Neue ID bestimmen
			Statement s = c.createStatement();
			String sql = "select max(id) from versuch";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int newID = rs.getInt(1) + 1;
			s.close();

			// Schueler erzeugen
			s = c.createStatement();
			sql = String.format("insert into versuch (id,s_id) values (%s,%s)",
					newID, id);
			s.executeUpdate(sql);
			s.close();
			return new SQLTry(newID);
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public void setFirstName(String nFirstName) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format(
					"update schueler set vorname='%s' where id=%s", nFirstName,
					id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public void setBirthday(GregorianCalendar nDate) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			int tag = nDate.get(Calendar.DAY_OF_MONTH);
			int monat = nDate.get(Calendar.MONTH) + 1;
			int jahr = nDate.get(Calendar.YEAR);

			String sql = String
					.format("update schueler set tag=%s, monat=%s, jahr=%s where id=%s",
							tag, monat, jahr, id);

			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public void setGender(Gender nGender) throws IOException {
		try {
			if (nGender == null)
				nGender = Gender.NOT_SPECIFIED;
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format(
					"update schueler set geschlecht='%s' where id=%s",
					nGender.ordinal(), id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public void setForm(String nClass) throws IOException {
		try {
			if (nClass == null)
				nClass = "";
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format(
					"update schueler set klasse='%s' where id=%s", nClass, id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	@Deprecated
	public void setDiscipline(int id, Discipline d) throws IOException {
		throw new NotImplementedException();
	}

	@Override
	public void setLastName(String string) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String
					.format("update schueler set nachname='%s' where id=%s",
							string, id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	@Override
	public String toString() {
		try {
			return String.format("(%3s): %s, %s\t\t [%s,%s,%s]", getId(),
					getLastName(), getFirstName(), getGender(), getForm(),
					DateFormat.getDateInstance()
							.format(getBirthday().getTime()));
		} catch (IOException ex) {
			ex.printStackTrace();
		}
		return super.toString();
	}

	@Override
	public int getNote() throws IOException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean hasEhrenurkunde() throws IOException {
		if(getGender().toString()=="m"){
			if(getBirthday().get(Calendar.YEAR)==2003){
				if(getPoints()>=775){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2002){
				if(getPoints()>=875){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2001){
				if(getPoints()>=975){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2000){
				if(getPoints()>=1050){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1999){
				if(getPoints()>=1125){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1998){
				if(getPoints()>=1225){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1997){
				if(getPoints()>=1325){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1996){
				if(getPoints()>=1400){
					return true;
				}
			}
		}
		if(getGender().toString()=="f"){
			if(getBirthday().get(Calendar.YEAR)==2003){
				if(getPoints()>=825){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2002){
				if(getPoints()>=900){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2001){
				if(getPoints()>=975){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2000){
				if(getPoints()>=1025){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1999){
				if(getPoints()>=1050){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1998){
				if(getPoints()>=1075){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1997){
				if(getPoints()>=1100){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1996){
				if(getPoints()>=1125){
					return true;
				}
			}
		}
		return false;
	}

	@Override
	public boolean hasSiegerurkunde() throws IOException {
		if(getGender().toString()=="m"){
			if(getBirthday().get(Calendar.YEAR)==2003){
				if(getPoints()>=600){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2002){
				if(getPoints()>=675){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2001){
				if(getPoints()>=750){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2000){
				if(getPoints()>=825){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1999){
				if(getPoints()>=900){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1998){
				if(getPoints()>=975){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1997){
				if(getPoints()>=1050){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1996){
				if(getPoints()>=1125){
					return true;
				}
			}
		}
		if(getGender().toString()=="f"){
			if(getBirthday().get(Calendar.YEAR)==2003){
				if(getPoints()>=625){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2002){
				if(getPoints()>=700){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2001){
				if(getPoints()>=775){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==2000){
				if(getPoints()>=825){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1999){
				if(getPoints()>=850){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1998){
				if(getPoints()>=875){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1997){
				if(getPoints()>=900){
					return true;
				}
			}
			if(getBirthday().get(Calendar.YEAR)==1996){
				if(getPoints()>=925){
					return true;
				}
			}
		}
		return false;
	}

	@Override
	public void setActive(boolean b) throws IOException{
		// TODO Auto-generated method stub
		
	}
}